package dao;

import classes.CargoComissao;
import classes.CargoEfetivo;
import classes.Lotacao;
import conexao.GerenciadorConexao;
import erro.ConexaoException;
import erro.ConsultaException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import classes.Servidor;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Roberta
 */
public class ServidorDao {

    private GerenciadorConexao g;

    public ServidorDao() {
        g = GerenciadorConexao.getInstancia();
    }

    /**
     * 
     * @param s (Servidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void inserir(Servidor s) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO servidor (matricula,nome,emailPessoal,telefone1,telefone2,telefoneSetor,emailProf,rua,bairro,cidade,numero,estado,cep,lotacao,cargoEfetivo,cargoComissao,cpf,login,nasc,nivelAcesso,telefoneFunc,senha,email) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, s.getMatricula());
            pstm.setString(2, s.getNome());
            pstm.setString(3, s.getEmailPessoal());
            pstm.setString(4, s.getTelefone1());
            pstm.setString(5, s.getTelefone2());
            pstm.setString(6, s.getTelefoneSetor());
            pstm.setString(7, s.getEmailProf());
            pstm.setString(8, s.getRua());
            pstm.setString(9, s.getBairro());
            pstm.setString(10, s.getCidade());
            pstm.setString(11, s.getNumero());
            pstm.setString(12, s.getEstado());
            pstm.setString(13, s.getCep());
            pstm.setInt(14, s.getLotacao().getLotacao_id());
            pstm.setInt(15, s.getCargoE().getCargoE_id());
            pstm.setInt(16, s.getCargoC().getCargoC_id());
            pstm.setString(17, s.getCpf());
            pstm.setString(18, s.getLogin());
            pstm.setDate(19, new java.sql.Date(s.getNasc().getTime()));
            pstm.setInt(20, s.getNivelAcesso());
            pstm.setString(21, s.getTelefoneFunc());
            pstm.setString(22, s.getSenha());
            pstm.setInt(23, s.getEmail());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public Servidor pesquisarMatricula(Servidor s) throws ConsultaException, ConexaoException {
        String sql = "SELECT matricula, nome, login FROM servidor WHERE matricula=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, s.getMatricula());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                s.setNome(rs.getString("nome"));
                s.setLogin(rs.getString("login"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return s;

    }

    /**
     * 
     * @param s (Servidor)
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public Servidor pesquisar(Servidor s) throws ConexaoException, ConsultaException {
        String sql = "SELECT *,lotacao.idlotacao AS lotacao,lotacao.sigla AS sigla,cargoefetivo.nome AS nomeCE, cargocomissao.nome AS nomeCC FROM servidor,lotacao,cargoefetivo,cargocomissao WHERE servidor.lotacao=lotacao.idlotacao AND cargoefetivo.idcargoEfetivo=servidor.cargoEfetivo AND cargocomissao.idcargoComissao=servidor.cargoComissao AND matricula=? ORDER BY servidor.nome";
        Lotacao l = new Lotacao();
        CargoEfetivo e = new CargoEfetivo();
        CargoComissao c = new CargoComissao();
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, s.getMatricula());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                s.setNome(rs.getString("nome"));
                s.setCpf(rs.getString("cpf"));
                s.setEmailPessoal(rs.getString("emailPessoal"));
                s.setTelefone1(rs.getString("telefone1"));
                s.setTelefone2(rs.getString("telefone2"));
                s.setTelefoneSetor(rs.getString("telefoneSetor"));
                s.setEmailProf(rs.getString("emailProf"));
                s.setRua(rs.getString("rua"));
                s.setBairro(rs.getString("bairro"));
                s.setCidade(rs.getString("cidade"));
                s.setNumero(rs.getString("numero"));
                s.setEstado(rs.getString("estado"));
                s.setCep(rs.getString("cep"));
                s.setNasc(rs.getDate("nasc"));
                s.setEmail(rs.getInt("email"));
                s.setNivelAcesso(rs.getInt("nivelAcesso"));
                s.setTelefoneFunc(rs.getString("telefoneFunc"));
                l.setLotacao_id(rs.getInt("lotacao"));
                l.setSigla(rs.getString("sigla"));
                s.setLotacao(l);
                e.setCargoE_id(rs.getInt("cargoEfetivo"));
                e.setNome(rs.getString("nomeCE"));
                s.setCargoE(e);
                c.setCargoC_id(rs.getInt("cargoComissao"));
                c.setNome(rs.getString("nomeCC"));
                s.setCargoC(c);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return s;
    }

    /**
     * 
     * @param s (Servidor)
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public Servidor pesquisarLogin(Servidor s) throws ConexaoException, ConsultaException {
        String sql = "SELECT matricula,nome,cpf,login,emailProf,senha,nivelAcesso FROM servidor WHERE login = ?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setString(1, s.getLogin());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                s.setMatricula(rs.getInt("matricula"));
                s.setNome(rs.getString("nome"));
                s.setCpf(rs.getString("cpf"));
                s.setLogin(rs.getString("login"));
                s.setEmailProf(rs.getString("emailProf"));
                s.setSenha(rs.getString("senha"));
                s.setNivelAcesso(rs.getInt("nivelAcesso"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return s;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Servidor> listar() throws ConexaoException, ConsultaException {
        List<Servidor> lista = new ArrayList<Servidor>();
        Servidor s;
        Lotacao l = new Lotacao();
        CargoEfetivo e = new CargoEfetivo();
        CargoComissao c = new CargoComissao();
        String sql = "SELECT *,lotacao.idlotacao AS lotacao,lotacao.sigla AS sigla,cargoefetivo.nome AS nomeCE, cargocomissao.nome AS nomeCC FROM servidor,lotacao,cargoefetivo,cargocomissao WHERE servidor.lotacao=lotacao.idlotacao AND cargoefetivo.idcargoEfetivo=servidor.cargoEfetivo AND cargocomissao.idcargoComissao=servidor.cargoComissao ORDER BY servidor.nome";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                s.setNome(rs.getString("nome"));
                s.setCpf(rs.getString("cpf"));
                s.setEmailPessoal(rs.getString("emailPessoal"));
                s.setTelefone1(rs.getString("telefone1"));
                s.setTelefone2(rs.getString("telefone2"));
                s.setTelefoneSetor(rs.getString("telefoneSetor"));
                s.setTelefoneFunc(rs.getString("telefoneFunc"));
                s.setEmailProf(rs.getString("emailProf"));
                s.setRua(rs.getString("rua"));
                s.setBairro(rs.getString("bairro"));
                s.setCidade(rs.getString("cidade"));
                s.setNumero(rs.getString("numero"));
                s.setEstado(rs.getString("estado"));
                s.setCep(rs.getString("cep"));
                s.setNasc(rs.getDate("nasc"));
                s.setEmail(rs.getInt("email"));
                s.setNivelAcesso(rs.getInt("nivelAcesso"));
                l.setLotacao_id(rs.getInt("lotacao"));
                l.setSigla(rs.getString("sigla"));
                s.setLotacao(l);
                e.setCargoE_id(rs.getInt("cargoEfetivo"));
                e.setNome(rs.getString("nomeCE"));
                s.setCargoE(e);
                c.setCargoC_id(rs.getInt("cargoComissao"));
                c.setNome(rs.getString("nomeCC"));
                s.setCargoC(c);
                lista.add(s);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Servidor> listarAtivos() throws ConexaoException, ConsultaException {
        List<Servidor> lista = new ArrayList<Servidor>();
        Servidor s;
        Lotacao l = new Lotacao();
        CargoEfetivo e = new CargoEfetivo();
        CargoComissao c = new CargoComissao();
        String sql = "SELECT *,lotacao.idlotacao AS lotacao,lotacao.sigla AS sigla,cargoefetivo.nome AS nomeCE, cargocomissao.nome AS nomeCC FROM servidor,lotacao,cargoefetivo,cargocomissao WHERE servidor.lotacao=lotacao.idlotacao AND cargoefetivo.idcargoEfetivo=servidor.cargoEfetivo AND cargocomissao.idcargoComissao=servidor.cargoComissao AND servidor.nivelAcesso<4 ORDER BY servidor.nome";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                s.setNome(rs.getString("nome"));
                s.setCpf(rs.getString("cpf"));
                s.setEmailPessoal(rs.getString("emailPessoal"));
                s.setTelefone1(rs.getString("telefone1"));
                s.setTelefone2(rs.getString("telefone2"));
                s.setTelefoneSetor(rs.getString("telefoneSetor"));
                s.setTelefoneFunc(rs.getString("telefoneFunc"));
                s.setEmailProf(rs.getString("emailProf"));
                s.setRua(rs.getString("rua"));
                s.setBairro(rs.getString("bairro"));
                s.setCidade(rs.getString("cidade"));
                s.setNumero(rs.getString("numero"));
                s.setEstado(rs.getString("estado"));
                s.setCep(rs.getString("cep"));
                s.setNasc(rs.getDate("nasc"));
                s.setEmail(rs.getInt("email"));
                s.setNivelAcesso(rs.getInt("nivelAcesso"));
                l.setLotacao_id(rs.getInt("lotacao"));
                l.setSigla(rs.getString("sigla"));
                s.setLotacao(l);
                e.setCargoE_id(rs.getInt("cargoEfetivo"));
                e.setNome(rs.getString("nomeCE"));
                s.setCargoE(e);
                c.setCargoC_id(rs.getInt("cargoComissao"));
                c.setNome(rs.getString("nomeCC"));
                s.setCargoC(c);
                lista.add(s);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Servidor> listarDesabilitados() throws ConexaoException, ConsultaException {
        List<Servidor> lista = new ArrayList<Servidor>();
        Servidor s;
        Lotacao l = new Lotacao();
        CargoEfetivo e = new CargoEfetivo();
        CargoComissao c = new CargoComissao();
        String sql = "SELECT *,lotacao.idlotacao AS lotacao,lotacao.sigla AS sigla,cargoefetivo.nome AS nomeCE, cargocomissao.nome AS nomeCC FROM servidor,lotacao,cargoefetivo,cargocomissao WHERE servidor.lotacao=lotacao.idlotacao AND cargoefetivo.idcargoEfetivo=servidor.cargoEfetivo AND cargocomissao.idcargoComissao=servidor.cargoComissao AND servidor.nivelAcesso=4 ORDER BY servidor.nome";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                s.setNome(rs.getString("nome"));
                s.setCpf(rs.getString("cpf"));
                s.setEmailPessoal(rs.getString("emailPessoal"));
                s.setTelefone1(rs.getString("telefone1"));
                s.setTelefone2(rs.getString("telefone2"));
                s.setTelefoneSetor(rs.getString("telefoneSetor"));
                s.setTelefoneFunc(rs.getString("telefoneFunc"));
                s.setEmailProf(rs.getString("emailProf"));
                s.setRua(rs.getString("rua"));
                s.setBairro(rs.getString("bairro"));
                s.setCidade(rs.getString("cidade"));
                s.setNumero(rs.getString("numero"));
                s.setEstado(rs.getString("estado"));
                s.setCep(rs.getString("cep"));
                s.setNasc(rs.getDate("nasc"));
                s.setEmail(rs.getInt("email"));
                s.setNivelAcesso(rs.getInt("nivelAcesso"));
                l.setLotacao_id(rs.getInt("lotacao"));
                l.setSigla(rs.getString("sigla"));
                s.setLotacao(l);
                e.setCargoE_id(rs.getInt("cargoEfetivo"));
                e.setNome(rs.getString("nomeCE"));
                s.setCargoE(e);
                c.setCargoC_id(rs.getInt("cargoComissao"));
                c.setNome(rs.getString("nomeCC"));
                s.setCargoC(c);
                lista.add(s);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param s (Servidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void deletar(Servidor s) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM servidor WHERE matricula =?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, s.getMatricula());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param s (Servidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void alterar(Servidor s) throws ConexaoException, ConsultaException {

        String sql = "UPDATE servidor SET nome=?,emailPessoal=?,telefone1=?,telefone2=?,telefoneSetor=?,emailProf=?,rua=?,bairro=?,cidade=?,numero=?,estado=?,cep=?,lotacao=?,cargoEfetivo=?,cargoComissao=?, nasc=?, nivelAcesso=?,cpf=?,telefoneFunc=?,email=? WHERE matricula=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);

            pstm.setString(1, s.getNome());
            pstm.setString(2, s.getEmailPessoal());
            pstm.setString(3, s.getTelefone1());
            pstm.setString(4, s.getTelefone2());
            pstm.setString(5, s.getTelefoneSetor());
            pstm.setString(6, s.getEmailProf());
            pstm.setString(7, s.getRua());
            pstm.setString(8, s.getBairro());
            pstm.setString(9, s.getCidade());
            pstm.setString(10, s.getNumero());
            pstm.setString(11, s.getEstado());
            pstm.setString(12, s.getCep());
            pstm.setInt(13, s.getLotacao().getLotacao_id());
            pstm.setInt(14, s.getCargoE().getCargoE_id());
            pstm.setInt(15, s.getCargoC().getCargoC_id());
            pstm.setDate(16, new java.sql.Date(s.getNasc().getTime()));
            pstm.setInt(17, s.getNivelAcesso());
            pstm.setString(18, s.getCpf());
            pstm.setString(19, s.getTelefoneFunc());
            pstm.setInt(20, s.getEmail());

            pstm.setInt(21, s.getMatricula());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param s (Servidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void criarSenha(Servidor s) throws ConexaoException, ConsultaException {

        String sql = "UPDATE servidor SET senha=? WHERE matricula=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);

            pstm.setString(1, s.getSenha());

            pstm.setInt(2, s.getMatricula());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }
}
